<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
				<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");

						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							//System.out.println("Insert!");
							PreparedStatement pstmt = conn.prepareStatement(
					                "INSERT INTO category VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
							pstmt.setInt(1, Integer.parseInt(request.getParameter("requirement_id")));    //requirement_id
							pstmt.setString(2, request.getParameter("category_name"));    
							pstmt.setInt(3, Integer.parseInt(request.getParameter("min_units"))); 
							pstmt.setString(4, request.getParameter("min_avg_grade")); 
					        pstmt.executeUpdate();
					        //conn.commit();
					        
					        ResultSet rs = pstmt.getGeneratedKeys();
					        
							int category_id = 0;
							if (rs.next()) {
								category_id = Integer.parseInt(rs.getString(1));
							}
							
							
					        String[] category_courses = request.getParameterValues("category_course");
					     	if (category_courses != null) {
					       		for (String course : category_courses) {
					        		pstmt = conn.prepareStatement(
					            		    "INSERT INTO category_course VALUES (?, ?)");
					        		pstmt.setInt(1, category_id);
					        		pstmt.setString(2, course);
					        		pstmt.executeUpdate();
					        	}
					     	}
					        
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							System.out.println("update!!!");
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // UPDATE the course attributes in the course table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "UPDATE category SET category_name = ?, min_units= ?, min_avg_grade= ? WHERE category_id = ?");

	                        pstmt.setString(1, request.getParameter("category_name"));
				            pstmt.setInt(2, Integer.parseInt(request.getParameter("min_units"))); 
				            pstmt.setString(3, request.getParameter("min_avg_grade"));
				            pstmt.setInt(4, Integer.parseInt(request.getParameter("category_id"))); 
	                        pstmt.executeUpdate();
	                        
	                        pstmt = conn.prepareStatement("DELETE FROM category_course WHERE category_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("category_id"))); 
	                     	pstmt.executeUpdate();

	                     	String[] category_courses = request.getParameterValues("category_course");
					     	if (category_courses != null) {
					       		for (String course : category_courses) {
					        		pstmt = conn.prepareStatement(
					            		    "INSERT INTO category_course VALUES (?, ?)");     		
					        		pstmt.setInt(1, Integer.parseInt(request.getParameter("category_id")));
					        		pstmt.setString(2, course);
					        		pstmt.executeUpdate();
					        	}
					     	}
	                        
	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("delete")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                        PreparedStatement pstmt = conn.prepareStatement("DELETE FROM category_course WHERE category_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("category_id")));
	                     	pstmt.executeUpdate();
	                     	
	                     	pstmt = conn.prepareStatement("DELETE FROM category WHERE category_id = ?");
	                        pstmt.setInt(1, Integer.parseInt(request.getParameter("category_id")));
	                     	pstmt.executeUpdate();

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
					
						// Create the statement
						Statement statement = conn.createStatement();
						ResultSet requirement = statement.executeQuery("SELECT * FROM degree_requirement WHERE title LIKE 'B%'");
						CachedRowSet req = new CachedRowSetImpl();
						req.populate(requirement);
						
						ResultSet rsCourse = statement.executeQuery("SELECT course_number " +
								"FROM course ORDER BY course_number");
						List<String> courses = new ArrayList<String>();
						while (rsCourse.next()) {
							courses.add(rsCourse.getString("course_number"));
						}
						
						statement = conn.createStatement();
						ResultSet concentration = statement.executeQuery("SELECT * FROM category");
						CachedRowSet rs = new CachedRowSetImpl();
						rs.populate(concentration);
						
				%>
				<table border="1">
					<tr>
						<th>Degree</th>
						<th>Category Name</th>	
						<th>Minimum Units</th>
						<th>Minimum Average Grade</th>
						<th>Category Course</th>
					</tr>
					<tr>
						<form action="undergrad_degree_requirement.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							
							<th>
							<select name="requirement_id" style="width:200px">		
							<%
								while(req.next()){
							%>
								<option value="<%=req.getInt("requirement_id") %>"><%=req.getString("title") %> (<%=req.getString("department")%>)</option>
							<%
								}
							%>
							</select>
							</th>
							<th><input type="text" value="" name="category_name" size="15"></th>
							<th><input type="text" value="" name="min_units" size="15"></th>
							<th><input type="text" value="" name="min_avg_grade" size="15"></th>
							<th>
								<select name="category_course" size="3" multiple="true" style="width:100px">
									<%
										for (String course : courses) {
									%>
											<option value="<%= course %>"><%= course %></option>
									<%
										}
									%>
								</select>
							</th>						
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
							// Iterate over the ResultSet
							while (rs.next()) {  
					%>
					<tr>
                        <form action="undergrad_degree_requirement.jsp" method="POST">
                            <input type="hidden" value="update" name="action">
                            <input type="hidden" value="<%= rs.getInt("category_id") %>" name="category_id">
                            <input type="hidden" value="<%= rs.getInt("requirement_id") %>" name="requirement_id">
                             
                            	<%
                            		PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM degree_requirement WHERE requirement_id = ?");
                        			pstmt.setInt(1, rs.getInt("requirement_id"));	
                        			ResultSet rsDegree = pstmt.executeQuery();
                        			//Set<String> prerequisites = new HashSet<String>();
                        			//while (rsPrerequisite.next()) {
                        				//prerequisites.add(rsDegree.getString("prerequisite_number"));
                        			//}
                        			if(rsDegree.next()){
                            	%>
                            			<td> <%= rsDegree.getString("title") %>(<%= rsDegree.getString("department")%>)</td>
                            			
                            	<%
                        			}
                            	%>
                            	<td>
                            	<input type="text" value="<%= rs.getString("category_name") %>" name="category_name">
                            	</td>
                            	<td>
                            	<input type="text" value="<%= rs.getInt("min_units") %>" name="min_units">
                            	</td>
                            	<td>
                            	<input type="text" value="<%= rs.getString("min_avg_grade") %>" name="min_avg_grade">
                            	</td>
                            	
                           		<td>
                            	<select name="category_course" size="3" multiple="true" style="width:100px">
								<%
									pstmt = conn.prepareStatement("SELECT course_number " +
											"FROM category_course WHERE category_id = ?");
									pstmt.setInt(1, rs.getInt("category_id"));
									ResultSet rsCatCourse = pstmt.executeQuery();
									Set<String> catCourses = new HashSet<String>();
									while(rsCatCourse.next()){
										catCourses.add(rsCatCourse.getString("course_number"));
									}
		 	
									for (String course : courses) {
								%>
									<option value="<%= course %>" 
								 			<% if (catCourses.contains(course)) out.print("selected='true'"); %>>
								 		<%= course %>
								 	</option>
								<%				 		
									}
								%>
								</select>
                            	</td>
                            <td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        
                        <form action="undergrad_degree_requirement.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                           <input type="hidden" value="<%=rs.getInt("category_id") %>" name="category_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
					<%
							}
					%>
					<%
						} catch (SQLException e) {
							System.out.println(e.getMessage());
							conn.rollback();
						} catch (Exception e) {
							System.out.println(e.getMessage());
							conn.rollback();
						}
					%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>



